SELECT *
FROM BPS_Patients bp
WHERE StatusText = 'Active'
AND InternalID IN (
	SELECT a.InternalID
	FROM APPOINTMENTS a
	INNER JOIN APPOINTMENTTYPES at
	ON at.APPOINTMENTCODE = a.APPOINTMENTTYPE
	INNER JOIN PATIENTS p
	ON bp.InternalID = p.INTERNALID
	AND p.MyMedicare = 0
	WHERE a.RECORDSTATUS = 1
	AND a.APPOINTMENTDATE >= CAST(GETDATE() AS DATE)
	AND a.APPOINTMENTDATE <= DATEADD(MONTH, 1, CAST(GETDATE() AS DATE))
	AND (
	   at.DESCRIPTION LIKE '%Care Plan%'
	OR at.DESCRIPTION LIKE '%GPCCM Plan%'
	OR at.DESCRIPTION LIKE '%GPCCM Plan Review%'
	)
)
ORDER BY surname, firstname;

-- Patients with an upcoming ‘Care Plan’ or 'GPCCMP' appointment booked, but not registered for MyMedicare